using System.Collections.Generic;
using System.Data.Entity;

namespace Abs.Core.DAL.SetUpDatabase
{
    internal partial class AbsInitializer
    {
        private void InitializeStoredProcedure(DbContext absContext)
        {
            foreach (var storedProcedureStatement in StoredProcedureStatements)
            {
                absContext.Database.ExecuteSqlCommand(storedProcedureStatement);
            }//end of foreach (StoredProcedureStatements)
        }//end of private void InitializeStoredProcedure()

        private IEnumerable<string> StoredProcedureStatements
        {
            get
            {
                var storedProcedures = new List<string>
                                   {
                                       @"CREATE PROCEDURE dbo.CreateUser
	                                        @Email nvarchar(128),
	                                        @Name nvarchar(Max),
	                                        @Password nvarchar(Max),
	                                        @RoleId int
                                           AS
	                                        INSERT INTO [AbsDb].[dbo].[User] 
	                                        VALUES (@Email, @Name, @Password, @RoleId);",
                                        ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.LogOn
	                                        @Email nvarchar(128),
	                                        @Password nvarchar(MAX)
                                           AS
	                                        SELECT *  
	                                        FROM [AbsDb].[dbo].[User]
	                                        WHERE Email = @Email
	                                        AND Password = @Password;",
                                        ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE EditUser
	                                        @Email nvarchar(128),
	                                        @Name nvarchar(MAX),
	                                        @RoleId int
                                           AS
	                                        UPDATE [AbsDb].[dbo].[User]
	                                        SET Name = @Name,
		                                        RoleId = @RoleId
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.EditUserRoleId
	                                        @Email nvarchar(128),
	                                        @RoleId int
                                           AS
	                                        UPDATE [AbsDb].[dbo].[User]
	                                        SET RoleId = @RoleId
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.EditUserName
	                                        @Email nvarchar(128),
	                                        @Name nvarchar(MAX)
                                           AS
	                                        UPDATE [AbsDb].[dbo].[User] 
	                                        SET Name = @Name
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.EditPassword
	                                        @Email nvarchar(128),
	                                        @Password nvarchar(MAX)
                                           AS
	                                        UPDATE [AbsDb].[dbo].[User]
	                                        SET Password = @Password
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.GetUsers
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[User];",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.GetUser
	                                        @Email nvarchar(128)
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[User]
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.DeleteUser
	                                        @Email nvarchar(128)
                                           AS
	                                        DELETE FROM [AbsDb].[dbo].[User]
	                                        WHERE Email = @Email;",
                                       ///////////////////////////////////////////////
                                       @"CREATE PROCEDURE dbo.CreateRoom
	                                        @Name nvarchar(MAX)
                                           AS
	                                        INSERT INTO [AbsDb].[dbo].[Room]
	                                        VALUES (@Name);",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.EditRoom
	                                        @RoomId int,
	                                        @Name nvarchar(MAX)
                                           AS
	                                        UPDATE [AbsDb].[dbo].[Room]
	                                        SET Name = @Name
	                                        WHERE RoomId = @RoomId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetRooms
                                           AS  
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[Room];",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetRoom
	                                        @RoomId int
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[Room]
	                                        WHERE RoomId = @RoomId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.DeleteRoom
	                                        @RoomId int
                                           AS
	                                        DELETE FROM [AbsDb].[dbo].[Room]
	                                        WHERE RoomId = @RoomId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.DeleteRoomScheduleByRoomId
	                                        @RoomId int
                                           AS
	                                        DELETE FROM [AbsDb].[dbo].[RoomSchedule]
	                                        WHERE RoomId = @RoomId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.CreateRoomSchedule
	                                        @RoomId int,
	                                        @DayId int,
	                                        @StartTime datetime,
	                                        @EndTime datetime
                                           AS
	                                        INSERT INTO [AbsDb].[dbo].[RoomSchedule]
	                                        VALUES (@RoomId, @DayId, @StartTime, @EndTime)",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.EditRoomSchedule
	                                        @RoomId int,
	                                        @DayId int,
	                                        @StartTime datetime,
	                                        @EndTime datetime
                                           AS
	                                        UPDATE [AbsDb].[dbo].[RoomSchedule]
	                                        SET StartTime = @StartTime,
		                                        EndTime = @EndTime
	                                        WHERE RoomId = @RoomId
	                                        AND DayId = @DayId",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetRoomSchedules
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[RoomSchedule];",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetRoomSchedule
	                                        @RoomId int,
	                                        @DayId int
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[RoomSchedule]
	                                        WHERE RoomId = @RoomId
	                                        AND DayId = @DayId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.DeleteRoomSchedule
	                                        @RoomId int,
	                                        @DayId int
                                           AS
	                                        DELETE FROM [AbsDb].[dbo].[RoomSchedule]
	                                        WHERE RoomId = @RoomId
	                                        AND DayId = @DayId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetRoomsOnDay
	                                        @DayId int
                                           AS
	                                        SELECT *
	                                        FROM RoomSchedule
	                                        WHERE DayId = @DayId",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.CreateAppointment
	                                        @UserEmail nvarchar(128),
	                                        @RoomId int,
	                                        @StartTime datetime,
	                                        @EndTime datetime,
	                                        @Comment nvarchar(MAX)
                                           AS
	                                        INSERT INTO [AbsDb].[dbo].[Appointment]
	                                        VALUES (@UserEmail, @RoomId, @StartTime, @EndTime, @Comment);",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.EditAppointment
	                                        @AppointmentId int,
	                                        @RoomId int,
	                                        @StartTime datetime,
	                                        @EndTime datetime,
	                                        @Comment nvarchar(MAX)
                                           AS
	                                        UPDATE [AbsDb].[dbo].[Appointment]
	                                        SET RoomId = @RoomId,
		                                        StartTime = @StartTime,
		                                        EndTime = @EndTime,
		                                        Comment = @Comment
	                                        WHERE AppointmentId = @AppointmentId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetAppointments
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[Appointment];",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetAppointment
	                                        @AppointmentId int
                                           AS
	                                        SELECT * 
	                                        FROM [AbsDb].[dbo].[Appointment]
	                                        WHERE AppointmentId = @AppointmentId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.DeleteAppointment
	                                        @AppointmentId int
                                           AS
	                                        DELETE FROM [AbsDb].[dbo].[Appointment]
	                                        WHERE AppointmentId = @AppointmentId;",
                                        ///////////////////////////////////////////////
                                        @"CREATE PROCEDURE dbo.GetIdentity
	                                        @tableName nvarchar(MAX)
                                           AS
	                                        SELECT IDENT_CURRENT (@tableName);"
                                   };//end of new List<string>
                return storedProcedures;
            }//end of get{}
        }//end of private IList<string> StoredProcedureStatements
    }//end of public partial class AbsInitializer
}//end of namespace ABS.DAL